package com.authine.cloudpivot.ext.controller.workList;

import cn.hutool.core.lang.Assert;
import com.authine.cloudpivot.engine.api.model.organization.UserModel;
import com.authine.cloudpivot.engine.api.model.runtime.BizObjectCreatedModel;
import com.authine.cloudpivot.engine.api.model.runtime.WorkItemModel;
import com.authine.cloudpivot.engine.api.model.runtime.WorkflowInstanceModel;
import com.authine.cloudpivot.engine.enums.status.SequenceStatus;
import com.authine.cloudpivot.ext.Utils.CustomSchemaCode;
import com.authine.cloudpivot.ext.service.CloudSqlService;
import com.authine.cloudpivot.web.api.controller.base.BaseController;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.ObjectUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * 待优化工作清单
 */

@Slf4j
@RestController
@RequestMapping("/public/OptimizedWorkList")
public class OptimizedWorkList extends BaseController {

    @Autowired
    CloudSqlService sqlService;

    /**
     * 待优化工作清单  审批流完成后 数据写到-待优化工作清单 基础表，补全基础表的数据
     */
    @RequestMapping("finish")
    public void finish(String bizId) {

        BizObjectCreatedModel bizObject = getBizObjectFacade().getBizObject(CustomSchemaCode.optimizedWorkList, bizId);

        //optimizedWorkListDetail  待优化工作清单子表
        List<Map<String, Object>> list = (List<Map<String, Object>>) bizObject.get(CustomSchemaCode.optimizedWorkListDetail);

        String now = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

        String approval = getFileBaseInfoWorkFlowApproval(bizObject);

        for (Map<String, Object> map : list) {
            try {
                //将子表中数据处理，随后更新至【待优化工作清单 基础表】
                Map<String, Object> data = fileInfoBaseMap(map, now, approval);

                //判断是否存在

                String id = existsBizObject01(map);
                if (StringUtils.isNotEmpty(id)) {
                    data.put("id", id);
                }

                BizObjectCreatedModel model = new BizObjectCreatedModel(CustomSchemaCode.optimizedWorkList_Basic, data, false);
                model.setSequenceStatus(SequenceStatus.COMPLETED.name());
                id = getBizObjectFacade().saveBizObject(CustomSchemaCode.adminUserId, model, false);
                log.info("待优化工作清单基础表-新增成功");
                //调用存储过程，入参id是指 待优化工作清单 基础表的表单id，每一条待优化工作清单的子表数据都会生成一条 待优化工作清单 基础表 的主表数据
                String fid = callProcess(id);

                String optimizedWorkListBasicTableName = getBizObjectFacade().getTableName(CustomSchemaCode.optimizedWorkList_Basic);
                StringBuffer updateFidSql = new StringBuffer("update ").append(optimizedWorkListBasicTableName).append(" set fid='")
                        .append(fid).append("' where id='").append(id).append("';");
                sqlService.update(updateFidSql.toString());

            } catch (Exception e) {
                log.info("待优化工作清单基础表--新增/修改--操作失败");
                log.info(e.getMessage(), e);
            }

        }
    }

    /**
     * 待优化工作清单 修改  审批流完成后 数据写到-待优化工作清单 基础表，补全基础表的数据
     */
    @RequestMapping("update")
    public void update(String bizId) {

        BizObjectCreatedModel bizObject = getBizObjectFacade().getBizObject(CustomSchemaCode.optimizedWorkListUpdate, bizId);

        //optimizedWorkListDetail  待优化工作清单子表
        List<Map<String, Object>> list = (List<Map<String, Object>>) bizObject.get(CustomSchemaCode.optimizedWorkListDetailUpda);

        String now = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

        String approval = getFileBaseInfoWorkFlowApproval(bizObject);

        for (Map<String, Object> map : list) {
            try {
                //将子表中数据处理，随后更新至【待优化工作清单 基础表】
                Map<String, Object> data = fileInfoBaseMap(map, now, approval);
                //判断是否已存在
                String optimizedWorkList_Basic_Id = existsBizObject(map);
                if (StringUtils.isNotBlank(optimizedWorkList_Basic_Id)) {
                    data.put("id", optimizedWorkList_Basic_Id);
                }

                BizObjectCreatedModel model = new BizObjectCreatedModel(CustomSchemaCode.optimizedWorkList_Basic, data, false);
                model.setSequenceStatus(SequenceStatus.COMPLETED.name());
                String id = getBizObjectFacade().saveBizObject(CustomSchemaCode.adminUserId, model, false);
                log.info("待优化工作清单基础表-新增成功");
                //调用存储过程，入参id是指 待优化工作清单 基础表的表单id，每一条待优化工作清单的子表数据都会生成一条 待优化工作清单 基础表 的主表数据
                String fid = callProcess(id);

                String optimizedWorkListBasicTableName = getBizObjectFacade().getTableName(CustomSchemaCode.optimizedWorkList_Basic);
                StringBuffer updateFidSql = new StringBuffer("update ").append(optimizedWorkListBasicTableName).append(" set fid='")
                        .append(fid).append("' where id='").append(id).append("';");
                sqlService.update(updateFidSql.toString());

            } catch (Exception e) {
                log.info("待优化工作清单基础表--新增/修改--操作失败");
                log.info(e.getMessage(), e);
            }

        }
    }

    @RequestMapping("toVoid")
    public void toVoid(String bizId) {
        BizObjectCreatedModel bizObject = getBizObjectFacade().getBizObject(CustomSchemaCode.voidOptimizedWorkList, bizId);
        List<Map<String, Object>> list = (List<Map<String, Object>>) bizObject.get("voidOptimizedWorkListDetail");
        String now = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        //获取审批人
        String approval = getFileBaseInfoWorkFlowApproval(bizObject);
        for (Map<String, Object> map : list) {
            String id = (String) map.get("eventObjectId");
            try {
                //判断是否已存在，如果不存在，放弃这条数据，直接跳过此次循环
                if (StringUtils.isBlank(id)) {
                    continue;
                }
                //获取数据
                Map<String, Object> data = fileInfoBaseMap(map, now, approval);
                data.put("id", id);
                BizObjectCreatedModel model = new BizObjectCreatedModel(CustomSchemaCode.optimizedWorkList_Basic, data, false);
                model.setSequenceStatus(SequenceStatus.CANCELED.name());
                id = getBizObjectFacade().saveBizObject(CustomSchemaCode.adminUserId, model, false);
                log.info("待优化工作清单基础表  作废操作成功");
                //调用存储过程
                callProcessToVoid(id);
            } catch (Exception e) {
                log.info("待优化工作清单基础表  作废操作失败，表单id：{}", id);
                log.info(e.getMessage(), e);
            }
        }
    }


    /**
     * -----待优化工作清单
     * exec  SyncToBestList
     *
     * @Companyid nvarchar(100),            ----公司FID
     * @item nvarchar(200),            ----事项
     * @RegisDate nvarchar(100),            ----登记日期
     * @DutyDeptCode nvarchar(100),            ----责任部门代码
     * @DutyPersonCode nvarchar(100),            ----责任人代码
     * @RelevantFileFID nvarchar(100),            ----相关档案FID
     * @DutyLeaderCode nvarchar(100),            ----平权领导代码
     * @WorkDesc nvarchar(300),            ----工作说明
     * @Auditor nvarchar(100)            ----审批人   02.0100 张三
     */
    private String callProcess(String bizId) {

        if (StringUtils.isEmpty(bizId)) {
            return bizId;
        }
        //编写sql
        String tableName = getBizObjectFacade().getTableName(CustomSchemaCode.optimizedWorkList_Basic);
        StringBuilder sql = new StringBuilder("SELECT * from ")
                .append(tableName).append(" where id ='")
                .append(bizId).append("';");
        //查询到入参
        Map<String, Object> map = sqlService.getMap(sql.toString());

        log.info("入参map={}", map);

        //调用存储过程
        String company = MapUtils.getString(map, "company", "");//公司FID
        String event = MapUtils.getString(map, "event", "");//事项

//        Date registrationDateTemp = (Date) map.get("registrationDate");//登记日期
//        String registrationDate = registrationDateTemp == null ? "" : DateFormatUtils.format(registrationDateTemp, "yyyy-MM-dd");
        LocalDateTime registrationDateTemp = (LocalDateTime) map.get("registrationDate");
        String registrationDate =  ObjectUtils.isEmpty(registrationDateTemp) ? "" : registrationDateTemp.toLocalDate().toString();


        String responsibleDeptFNumber = MapUtils.getString(map, "responsibleDeptFNumber", "");//责任部门代码
        String personNumber = MapUtils.getString(map, "personNumber", "");//责任人代码
        String relatedFileFID = MapUtils.getString(map, "relatedFileFID", "");//相关档案FID
        String affirmativeLeaderCode = MapUtils.getString(map, "affirmativeLeaderCode", "");//平权领导代码
        String workDesc = MapUtils.getString(map, "workDesc", "");//工作说明（非必填）
        String approval = MapUtils.getString(map, "approval", "");//审批人   02.0100 张三
        log.info("approval审批人：{}", approval);
        String fid = MapUtils.getString(map, "fid", "");//fid

//        Assert.isFalse(StringUtils.isEmpty(company), "{}不能为空", "company");
//        Assert.isFalse(StringUtils.isEmpty(event), "{}不能为空", "event");
//        Assert.isFalse(StringUtils.isEmpty(registrationDate), "{}不能为空", "registrationDate");
//        Assert.isFalse(StringUtils.isEmpty(responsibleDeptFNumber), "{}不能为空", "responsibleDeptFNumber");
//        Assert.isFalse(StringUtils.isEmpty(personNumber), "{}不能为空", "personNumber");
//        Assert.isFalse(StringUtils.isEmpty(relatedFileFID), "{}不能为空", "relatedFileFID");
//        Assert.isFalse(StringUtils.isEmpty(affirmativeLeaderCode), "{}不能为空", "affirmativeLeaderCode");


//        String execSql = String.format("exec [HG_LINK].[hg].[dbo].SyncToBestList '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s'",
//                company, event, registrationDate, responsibleDeptFNumber, personNumber, relatedFileFID, affirmativeLeaderCode, workDesc, approval);
//
//        log.info("\n==========准备调用[待优化工作清单]存储过程:{}", execSql);
//
//        sqlService.execute(CloudSqlService.htEas, execSql);

        JdbcTemplate jdbcTemplate = sqlService.getJdbcTemplateByDbCode(CloudSqlService.htEas);

        String returnValue = (String) jdbcTemplate.execute(
                new CallableStatementCreator() {
                    public CallableStatement createCallableStatement(Connection con) throws SQLException {
                        CallableStatement cs = con.prepareCall("{call [HG_LINK].[hg].[dbo].SyncToBestList   (?,?,?,?,?,?,?,?,?,?)}");
                        // 设置输入参数的值
                        cs.setString(1, company);
                        cs.setString(2, event);
                        cs.setString(3, registrationDate);
                        cs.setString(4, responsibleDeptFNumber);
                        cs.setString(5, personNumber);
                        cs.setString(6, relatedFileFID);
                        cs.setString(7, affirmativeLeaderCode);

                        cs.setString(8, workDesc);
                        cs.setString(9, approval);
                        cs.setString(10, fid);
                        cs.registerOutParameter(10, Types.VARCHAR);// 注册输出参数的类型
                        return cs;
                    }
                }, new CallableStatementCallback() {
                    public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                        cs.execute();
                        String fid = cs.getString(10);
                        return fid;// 获取输出参数的值
                    }
                });

        log.info("\n=============[待优化工作清单]存储过程执行完成，返回的fid:{}", returnValue);
        return returnValue;

    }

    /**
     * ----待优化工作清单 作废
     * exec   ToBestListDisable
     *
     * @param bizId
     * @Companyid nvarchar(100),            ----公司FID
     * @item nvarchar(200),            ----事项
     * @RegisDate nvarchar(100),            ----登记日期
     * @RelevantFileFID nvarchar(100)            ----相关档案FID
     */
    private void callProcessToVoid(String bizId) {

        if (StringUtils.isEmpty(bizId)) {
            return;
        }
        //编写sql
        String tableName = getBizObjectFacade().getTableName(CustomSchemaCode.optimizedWorkList_Basic);
        StringBuilder sql = new StringBuilder("SELECT company,personNumber from ")
                .append(tableName).append(" where id ='")
                .append(bizId).append("';");
        //查询到入参
        Map<String, Object> map = sqlService.getMap(sql.toString());
        log.info("入参map={}", map);

        //准备调用存储过程的入参
        String fid = MapUtils.getString(map, "fid", "");//公司FID

//        //拼接sql
//        String execSql = String.format("exec [HG_LINK].[hg].[dbo].ToBestListDisable '%s','%s','%s','%s'",
//                company, event, registrationDate, relatedFileFID);
//
//        log.info("\n==========准备调用[待优化工作清单   作废]存储过程:{}", execSql);
//        sqlService.execute(CloudSqlService.htEas, execSql);

        JdbcTemplate jdbcTemplate = sqlService.getJdbcTemplateByDbCode(CloudSqlService.htEas);
        jdbcTemplate.execute(
                new CallableStatementCreator() {
                    public CallableStatement createCallableStatement(Connection con) throws SQLException {
                        CallableStatement cs = con.prepareCall("{call [HG_LINK].[hg].[dbo].ToBestListDisable (?)}");
                        // 设置输入参数的值
                        cs.setString(1, fid);
                        return cs;
                    }
                }, new CallableStatementCallback() {
                    public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                        cs.execute();
                        return "success";// 获取输出参数的值
                    }
                });
        log.info("\n=============[待优化工作清单   作废]存储过程执行完成");

    }

    /**
     * 查询审批人,返回  员工号+姓名
     *
     * @param bizObject
     * @return
     */
    private String getFileBaseInfoWorkFlowApproval(BizObjectCreatedModel bizObject) {
        WorkflowInstanceModel instanceModel = getWorkflowInstanceFacade().getByObjectId(bizObject.getId());
        List<WorkItemModel> workItems = getWorkflowInstanceFacade().getWorkItems(instanceModel.getId(), true);
        final String finalActivityCode = "Activity18";
        Optional<WorkItemModel> first = workItems.stream().filter(a -> a.getActivityCode().equals(finalActivityCode)).findFirst();
        String participant = null;
        if (first.isPresent()) {
            WorkItemModel workItemModel = first.get();
            participant = workItemModel.getParticipant();
        }


        if (participant == null) {
            participant = bizObject.getCreater().getId();
        }

        UserModel user = getOrganizationFacade().getUser(participant);


        return new StringBuilder(user.getEmployeeNo()).append("　").append(user.getName()).toString();
    }

    /**
     * 判断是否已存在
     *
     * @return
     */
    private String existsBizObject01(Map data) {

        //
        String companyNumber = (String) data.get("companyNumber");//公司
        String functionFNumber = (String) data.get("functionFNumber");//职能
//        String firstCode = (String) data.get("firstCode");//一级序号
//        String secondCode = (String) data.get("secondCode");//二级序号
        String responsibleDeptFNumber = (String) data.get("responsibleDeptFNumber");//责任部门
        String event = (String) data.get("event");//事项

        String tableName = getBizObjectFacade().getTableName(CustomSchemaCode.NORMAL_WORK_LIST_BASE);

//        StringBuilder sql = new StringBuilder("select id  from ").append(tableName).append(" where id ='").append(eventObjectId).append("'");

        StringBuilder sql = new StringBuilder("select id  from ").append(tableName)
                .append(" where companyNumber='").append(companyNumber)
                .append("' and functionFNumber='").append(functionFNumber)
//                .append("' and firstCode='").append(firstCode)
//                .append("' and secondCode='").append(secondCode)
                .append("' and responsibleDeptFNumber='").append(responsibleDeptFNumber)
                .append("' and event='").append(event)
                .append("';");

        Map<String, Object> map = sqlService.getMap(sql.toString());

        return (String) map.get("id");
    }
    /**
     * 判断是否已存在
     *
     * @return
     */
    private String existsBizObject(Map data) {

        //事项基础表id
        String eventObjectId = (String) data.get("eventObjectId");

        String tableName = getBizObjectFacade().getTableName(CustomSchemaCode.optimizedWorkList_Basic);

        StringBuilder sql = new StringBuilder("select id  from ").append(tableName).append(" where id ='").append(eventObjectId).append("'");

        Map<String, Object> map = sqlService.getMap(sql.toString());

        return (String) map.get("id");
    }

    /**
     * 转换成  待优化工作清单 基础表 的数据
     *
     * @param map
     * @param auditDate
     * @return
     */
    private Map<String, Object> fileInfoBaseMap(Map<String, Object> map, String auditDate, String approval) {
        Map<String, Object> data = new HashMap<>();
        //审批人
        data.put("approval", approval);

        //公司
        data.put("company", map.get("company"));
        //公司代码隐藏
        data.put("companyNumber", map.get("companyNumber"));
        //事项
        data.put("event", map.get("event"));
        //登记日期
        data.put("registrationDate", map.get("registrationDate"));
        //责任部门代码关联
        data.put("responsibleDeptFid", map.get("responsibleDeptFid"));
        //责任部门代码隐藏
        data.put("responsibleDeptFNumber", map.get("responsibleDeptFNumber"));
        //责任部门
        data.put("responsibleDeptName", map.get("responsibleDeptName"));
        //责任人代码关联
        data.put("responsiblePerson", map.get("responsiblePerson"));
        //责任人代码隐藏
        data.put("personNumber", map.get("personNumber"));
        //责任人
        data.put("personName", map.get("personName"));
        //职能代码关联
        data.put("function", map.get("function"));
        //职能代码
        data.put("functionFNumber", map.get("functionFNumber"));
        //相关档案类型关联
        data.put("relatedFileType", map.get("relatedFileType"));
        //相关档案类型代码隐藏
        data.put("relatedFileTypeFNumber", map.get("relatedFileTypeFNumber"));
        //相关档案代码
        data.put("relatedFileFID", map.get("relatedFileFID"));
        //相关档案代码隐藏
        data.put("relatedFileCode", map.get("relatedFileCode"));

        //相关档案名称
        data.put("relevantFileName", map.get("relevantFileName"));
        //相关档案备注
        data.put("relevantFileRemarks", map.get("relevantFileRemarks"));
        //相关档案说明
        data.put("relatedFileDescription", map.get("relatedFileDescription"));
        //相关档案公司主责部门关联
        data.put("relevantFilesMainDept", map.get("relevantFilesMainDept"));
        //相关档案公司主责部门代码
        data.put("relevantFilesMainDeptNumber", map.get("relevantFilesMainDeptNumber"));
        //相关档案公司主责岗关联
        data.put("relevantFilesMainPosition", map.get("relevantFilesMainPosition"));
        //相关档案公司主责岗代码
        data.put("relevFilesMainPositionNumber", map.get("relevFilesMainPositionNumber"));
        //平权领导代码
        data.put("affirmativeLeader", map.get("affirmativeLeader"));
        //平权领导代码隐藏
        data.put("affirmativeLeaderCode", map.get("affirmativeLeaderCode"));
        //平权领导名称
        data.put("affirmativeLeaderName", map.get("affirmativeLeaderName"));
        //工作说明
        data.put("workDesc", map.get("workDesc"));
        //子表id
        data.put("childTableID", map.get("id"));
        return data;
    }

}
